﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace IMLibrary.MySqlData
{
    public sealed class SqlParameterClass
   {  
       #region private methods, variables, and constructors  
  
       //Since this class provides only static methods, make the default constructor private to prevent   
       //instances from being created with "new SqlServerClass()".  
       private SqlParameterClass() { }  
  
      private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());  
  
       /// <summary>  
      /// resolve at run time the appropriate set of SqlParameters for a stored procedure  
     /// </summary>  
      /// <param name="connectionString">a valid connection string for a SqlConnection</param>  
      /// <param name="spName">the name of the stored procedure</param>  
       /// <param name="includeReturnValueParameter">whether or not to include their return value parameter</param>  
      /// <returns></returns>  
       private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
       {  
           using (SqlConnection cn = new SqlConnection(connectionString))  
           using (SqlCommand cmd = new SqlCommand(spName, cn))  
          {  
              cn.Open();  
             cmd.CommandType = CommandType.StoredProcedure;  
 
              SqlCommandBuilder.DeriveParameters(cmd);  
  
              if (!includeReturnValueParameter)  
               {  
                  cmd.Parameters.RemoveAt(0);  
              }  
   
              SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count]; ;  
  
               cmd.Parameters.CopyTo(discoveredParameters, 0);  
   
               return discoveredParameters;  
           }  
       }  
  
      //deep copy of cached SqlParameter array  
       private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
      {  
           SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];  
  
           for (int i = 0, j = originalParameters.Length; i<j; i++)  
           {  
              clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();  
           }  
  
           return clonedParameters;  
       }  
  
       #endregion private methods, variables, and constructors  
 
      #region caching functions  
   
       /// <summary>  
       /// add parameter array to the cache  
       /// </summary>  
       /// <param name="connectionString">a valid connection string for a SqlConnection</param>  
       /// <param name="commandText">the stored procedure name or T-SQL command</param>  
       /// <param name="commandParameters">an array of SqlParamters to be cached</param>  
      public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters)
       {  
           string hashKey = connectionString + ":" + commandText;  
   
           paramCache[hashKey] = commandParameters;  
       }  
   
       /// <summary>  
      /// retrieve a parameter array from the cache  
      /// </summary>  
       /// <param name="connectionString">a valid connection string for a SqlConnection</param>  
       /// <param name="commandText">the stored procedure name or T-SQL command</param>  
      /// <returns>an array of SqlParamters</returns>  
       public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
      {  
           string hashKey = connectionString + ":" + commandText;  
   
          SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey];  
   
          if (cachedParameters == null)  
          {  
              return null;  
           }  
          else  
           {  
              return CloneParameters(cachedParameters);  
          }  
       }  
  
       #endregion caching functions  
  
      #region Parameter Discovery Functions  
   
       /// <summary>  
       /// Retrieves the set of SqlParameters appropriate for the stored procedure  
      /// </summary>  
       /// <remarks>  
       /// This method will query the database for this information, and then store it in a cache for future requests.  
       /// </remarks>  
      /// <param name="connectionString">a valid connection string for a SqlConnection</param>  
      /// <param name="spName">the name of the stored procedure</param>  
       /// <returns>an array of SqlParameters</returns>  
       public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
      {  
           return GetSpParameterSet(connectionString, spName, false);  
       }  
   
      /// <summary>  
       /// Retrieves the set of SqlParameters appropriate for the stored procedure  
      /// </summary>  
      /// <remarks>  
      /// This method will query the database for this information, and then store it in a cache for future requests.  
       /// </remarks>  
       /// <param name="connectionString">a valid connection string for a SqlConnection</param>  
       /// <param name="spName">the name of the stored procedure</param>  
      /// <param name="includeReturnValueParameter">a bool value indicating whether the return value parameter should be included in the results</param>  
      /// <returns>an array of SqlParameters</returns>  
       public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
      {  
           string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");  
   
           SqlParameter[] cachedParameters;  
  
           cachedParameters = (SqlParameter[])paramCache[hashKey];  
   
          if (cachedParameters == null)  
           {  
              cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter));  
          }  
   
           return CloneParameters(cachedParameters);  
       }  
  
       #endregion Parameter Discovery Functions  
   }  

}
